﻿using CBS.Model.ManageModel;
using ClosedXML.Excel;
using DocumentFormat.OpenXml.Spreadsheet;
using NPOI.POIFS.Crypt.Dsig;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CBS.APP
{
    public class StatementExportApp
    {
        public static void StudentSignInAndPaymentInfoExport(string filePath, DataSet ds)
        {
            using (var workbook = new XLWorkbook())
            {
                for (int j = 0; j < ds.Tables.Count; j++)
                {
                    var data = ds.Tables[j];
                    var worksheet = workbook.Worksheets.Add(data.TableName);

                    // 添加查询条件
                    int currentRow = 1;
                    worksheet.Cell(currentRow, 1).Value = data.TableName;
                    worksheet.Cell(currentRow, 1).Style.Fill.BackgroundColor = XLColor.Yellow;

                    // 添加空行
                    currentRow++;

                    // 添加表头
                    for (int i = 0; i < data.Columns.Count; i++)
                    {
                        worksheet.Cell(currentRow, i + 1).Value = data.Columns[i].ColumnName;
                        worksheet.Cell(currentRow, i + 1).Style.Font.Bold = true;
                    }


                    // 添加数据
                    currentRow++;
                    foreach (DataRow row in data.Rows)
                    {
                        for (int i = 0; i < data.Columns.Count; i++)
                        {
                            worksheet.Cell(currentRow, i + 1).Value = row[i].ToString();
                        }
                        currentRow++;
                    }

                    // 自动调整列宽
                    worksheet.Columns().AdjustToContents();
                }



                // 保存文件
                workbook.SaveAs(filePath);
            }
        }

        public static void StudentSignInAndPaymentInfoExport(string filePath, List<StudentSignStatementViewModel> signData)
        {
            Dictionary<int, XLColor> PreSetColor = new Dictionary<int, XLColor>
            {
                { 0, XLColor.UclaBlue },
                { 1, XLColor.SkyBlue },
                { 2, XLColor.LightBrown },
                { 3, XLColor.Orange },
                { 4, XLColor.FromArgb(204, 153, 0) },
                { 5, XLColor.FromArgb(0, 102, 153) },
            };

            var titleColor = XLColor.FromArgb(0, 153, 0);
            using (var workbook = new XLWorkbook())
            {
                for (int j = 0; j < signData.Count; j++)
                {
                    var data = signData[j];
                    var worksheet = workbook.Worksheets.Add(data.StudentName);
                    worksheet.Style.Font.FontSize = 12;
                    worksheet.Style.Border.TopBorder = XLBorderStyleValues.Dotted;
                    worksheet.Style.Border.LeftBorder = XLBorderStyleValues.Dotted;
                    worksheet.Style.Border.RightBorder = XLBorderStyleValues.Dotted;
                    worksheet.Style.Border.BottomBorder = XLBorderStyleValues.Dotted;
                    // 添加查询条件
                    int currentRow = 1;
                    worksheet.Cell(currentRow, 1).Value = data.StudentName;
                    worksheet.Cell(currentRow, 1).Style.Font.Bold = true;
                    worksheet.Cell(currentRow, 1).Style.Font.FontSize = 14;
                    worksheet.Cell(currentRow, 1).Style.Fill.BackgroundColor = XLColor.Yellow;
                    worksheet.Range(1, 1, 1, 2).Merge();
                    // 添加空行
                    currentRow++;

                    // 添加表头
                    for (int i = 0; i < data.CourseSignDetail.Count; i++)
                    {
                        worksheet.Cell(currentRow, i * 4 + 1).Value = "序号";
                        worksheet.Cell(currentRow, i * 4 + 1).Style.Font.FontSize = 14;
                        worksheet.Cell(currentRow, i * 4 + 1).Style.Font.Bold = true;
                        worksheet.Cell(currentRow, i * 4 + 1).Style.Fill.BackgroundColor = titleColor;
                        worksheet.Cell(currentRow, i * 4 + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        worksheet.Cell(currentRow, i * 4 + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        worksheet.Column(i * 4 + 1).Width = 5;

                        worksheet.Cell(currentRow, i * 4 + 2).Value = "日期";
                        worksheet.Cell(currentRow, i * 4 + 2).Style.Font.FontSize = 14;
                        worksheet.Cell(currentRow, i * 4 + 2).Style.Font.Bold = true;
                        worksheet.Cell(currentRow, i * 4 + 2).Style.Fill.BackgroundColor = titleColor;
                        worksheet.Cell(currentRow, i * 4 + 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        worksheet.Cell(currentRow, i * 4 + 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        worksheet.Column(i * 4 + 2).Width = 8;

                        worksheet.Cell(currentRow, i * 4 + 3).Value = "时间";
                        worksheet.Cell(currentRow, i * 4 + 3).Style.Font.FontSize = 14;
                        worksheet.Cell(currentRow, i * 4 + 3).Style.Font.Bold = true;
                        worksheet.Cell(currentRow, i * 4 + 3).Style.Fill.BackgroundColor = titleColor;
                        worksheet.Cell(currentRow, i * 4 + 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        worksheet.Cell(currentRow, i * 4 + 3).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        worksheet.Column(i * 4 + 3).Width = 16;

                        worksheet.Cell(currentRow, i * 4 + 4).Value = "课程";
                        worksheet.Cell(currentRow, i * 4 + 4).Style.Font.FontSize = 14;
                        worksheet.Cell(currentRow, i * 4 + 4).Style.Font.Bold = true;
                        worksheet.Cell(currentRow, i * 4 + 4).Style.Fill.BackgroundColor = titleColor;
                        worksheet.Cell(currentRow, i * 4 + 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        worksheet.Cell(currentRow, i * 4 + 4).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        worksheet.Column(i * 4 + 4).Width = 18;
                    }

                    worksheet.Cell(currentRow, data.CourseSignDetail.Count * 4 + 1).Value = "缴费情况";
                    worksheet.Cell(currentRow, data.CourseSignDetail.Count * 4 + 1).Style.Font.FontSize = 14;
                    worksheet.Cell(currentRow, data.CourseSignDetail.Count * 4 + 1).Style.Font.Bold = true;
                    worksheet.Cell(currentRow, data.CourseSignDetail.Count * 4 + 1).Style.Fill.BackgroundColor = titleColor;
                    worksheet.Cell(currentRow, data.CourseSignDetail.Count * 4 + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    worksheet.Cell(currentRow, data.CourseSignDetail.Count * 4 + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                    worksheet.Range(currentRow, data.CourseSignDetail.Count * 4 + 1, currentRow, data.CourseSignDetail.Count * 4 + 3).Merge();

                    worksheet.Column(data.CourseSignDetail.Count * 4 + 1).Width = 20;
                    worksheet.Column(data.CourseSignDetail.Count * 4 + 2).Width = 20;
                    worksheet.Column(data.CourseSignDetail.Count * 4 + 3).Width = 20;

                    List<int> rowCount = new List<int>();
                    foreach (var item in data.CourseSignDetail)
                    {
                        if (item == null || item.SignDetail== null) continue;
                        rowCount.Add(item.SignDetail.Count);
                    }
                    int maxCount = rowCount.Max();

                    // 添加数据
                    int totalRow = currentRow++;
                    
                    for (int i = 0; i < maxCount; i++)
                    {
                        for (int k = 0; k < data.CourseSignDetail.Count; k++)
                        {
                            if (data.CourseSignDetail[k].SignDetail == null) continue;
                            if (i < data.CourseSignDetail[k].SignDetail.Count)
                            {
                                worksheet.Cell(currentRow + i, k * 4 + 1).Style.Font.Bold = false;
                                worksheet.Cell(currentRow + i, k * 4 + 1).Value = (i + 1).ToString();
                                worksheet.Cell(currentRow + i, k * 4 + 1).Style.Fill.BackgroundColor = PreSetColor[k % 6];

                                worksheet.Cell(currentRow + i, k * 4 + 2).Style.Font.Bold = false;
                                worksheet.Cell(currentRow + i, k * 4 + 2).Value = $"{data.CourseSignDetail[k].SignDetail[i].SignDetailStartTime.ToString("MM.dd")}";
                                worksheet.Cell(currentRow + i, k * 4 + 2).Style.Fill.BackgroundColor = PreSetColor[k % 6];

                                worksheet.Cell(currentRow + i, k * 4 + 3).Style.Font.Bold = false;
                                worksheet.Cell(currentRow + i, k * 4 + 3).Value = $"{data.CourseSignDetail[k].SignDetail[i].SignDetailStartTime.ToString("HH:mm")} - {data.CourseSignDetail[k].SignDetail[i].SignDetailEndTime.ToString("HH:mm")}";
                                worksheet.Cell(currentRow + i, k * 4 + 3).Style.Fill.BackgroundColor = PreSetColor[k % 6];

                                worksheet.Cell(currentRow + i, k * 4 + 4).Style.Font.Bold = false;
                                worksheet.Cell(currentRow + i, k * 4 + 4).Value = $"{data.CourseSignDetail[k].SignDetail[i].ClassName}-{data.CourseSignDetail[k].CourseName}";
                                
                                string classNameStr = $"{data.CourseSignDetail[k].SignDetail[i].ClassName}-{data.CourseSignDetail[k].CourseName}";
                                if (data.CourseSignDetail[k].SignDetail[i].IsTaster) 
                                {
                                    classNameStr = classNameStr + "-试听课";
                                }
                                if (data.CourseSignDetail[k].SignDetail[i].IsSupplement) 
                                {
                                    classNameStr = classNameStr + "-补课";
                                }

                                worksheet.Cell(currentRow + i, k * 4 + 4).Value = classNameStr;
                                worksheet.Cell(currentRow + i, k * 4 + 4).Style.Fill.BackgroundColor = PreSetColor[k % 6];

                            }
                            else
                            {

                            }
                        }
                    }

                    string courseStr = "";
                    foreach (var item in data.CourseSignDetail)
                    {
                        if (item.SignDetail == null)
                        {
                            continue;
                        }
                        courseStr += string.Format("\r\n{0}{1}{2}次,", data.GradeName, item.CourseName, item.SignDetail.Count);
                    }

                    string payStr = "";
                    if (data.StudentPaymentDetail != null && data.StudentPaymentDetail.Count > 0)
                    {
                        foreach (var item in data.StudentPaymentDetail)
                        {
                            payStr += string.Format("{0}在{1}日现金缴费{2}元，\r\n", data.StudentName, item.PayTime.ToString("yyyy-MM-dd"), item.Payment);
                        }
                    }

                    string beforeAmount = "";
                    if (data.BeforeAmount > 0)
                    {
                        beforeAmount = $"{data.StudentName}还有余款{data.BeforeAmount}\r\n元，";
                    }


                    bool fullSignin = true;
                    string fullSigninStr = "";
                    if (data.CourseSignDetail != null)
                    {
                        foreach (var item in data.CourseSignDetail)
                        {
                            if (item.SignDetail == null || item.LessonDetail == null)
                            {
                                continue ;
                            }

                            if (item.SignDetail.Count < item.LessonDetail.Count)
                            {
                                fullSignin = false;
                                break;
                            }
                        }
                        
                        if (fullSignin)
                        {
                            fullSigninStr = $"\r\n{data.StudentName}{data.CoursePlanName.Replace(data.StudentName,"")}积极努力，\r\n坚持不懈，不畏酷暑，风雨无阻\r\n，特奖励200元，希望{data.StudentName}同学\r\n在{data.GradeName}这一年蓄势待发!披荆斩棘!一路向前!不负众望!\r\n";
                        }
                    }
                    

                    string paymentStr = "";
                    if (data.Payment > 0)
                    {
                        paymentStr = $"\r\n{data.StudentName}{data.CoursePlanName.Replace(data.StudentName, "")}实际预付款为:{data.Payment}元，";
                    }

                    if (data.BeforeAmount > 0) { paymentStr = $" + {data.BeforeAmount} = {data.Payment + data.BeforeAmount}"; }
                    if (fullSignin)
                    {
                        paymentStr += $" + 200(全勤奖) = {data.Payment + data.BeforeAmount + 200}";
                    }

                    decimal pay = data.Payment + data.BeforeAmount;
                    if (fullSignin)
                    {
                        pay += 200;
                    }
                    string summeryStateStr = "";
                    if (pay >= data.Payable)
                    {
                        summeryStateStr = @$"{ data.StudentName}截止到{ data.EndTime.ToString("yyyy-MM-dd")}日{data.CoursePlanName.Replace(data.StudentName, "")}所有费用已结清。";
                    }
                    else
                    {
                        summeryStateStr = @$"{data.StudentName}截止到{data.EndTime.ToString("yyyy-MM-dd")}日{data.CoursePlanName.Replace(data.StudentName, "")}所有费用欠款{data.Payable - pay}元";
                    }

                    string summery = @$"{data.StudentName}{data.CoursePlanName.Replace(data.StudentName, "")}从{data.StartTime.ToString("yyyy-MM-dd")} ~ {data.EndTime.ToString("MM-dd")}日,
总计：{courseStr}
总计费用为{data.Payable}元。
";//{fullSigninStr}{payStr}{beforeAmount}{paymentStr}{summeryStateStr}";

                    worksheet.Cell(totalRow + 1, data.CourseSignDetail.Count * 4 + 1).Value = summery;
                    worksheet.Cell(totalRow + 1, data.CourseSignDetail.Count * 4 + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    worksheet.Cell(totalRow + 1, data.CourseSignDetail.Count * 4 + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;

                    worksheet.Range(totalRow + 1, data.CourseSignDetail.Count * 4 + 1, totalRow + maxCount, data.CourseSignDetail.Count * 4 + 3).Merge();

                    var range = worksheet.Range(1,1, totalRow + maxCount, data.CourseSignDetail.Count * 4 + 3);
                    range.Style.Border.TopBorder = XLBorderStyleValues.Thin;
                    range.Style.Border.TopBorderColor = XLColor.Black;
                    range.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
                    range.Style.Border.LeftBorderColor = XLColor.Black;
                    range.Style.Border.RightBorder = XLBorderStyleValues.Thin;
                    range.Style.Border.RightBorderColor = XLColor.Black;
                    range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
                    range.Style.Border.BottomBorderColor = XLColor.Black;

                }
                // 保存文件
                workbook.SaveAs(filePath);
            }
        }
    }
}
